package com.elite;

import java.io.ByteArrayInputStream;
import java.io.ByteArrayOutputStream;
import  java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.io.InputStream;
import java.math.BigDecimal;
import java.text.SimpleDateFormat;
import java.util.List;
import java.util.Map;

import org.apache.poi.hssf.usermodel.HSSFCell;
import org.apache.poi.hssf.usermodel.HSSFDateUtil;
import org.apache.poi.hssf.usermodel.HSSFRow;
import org.apache.poi.hssf.usermodel.HSSFSheet;
import org.apache.poi.hssf.usermodel.HSSFWorkbook;

import  jxl.Cell;
import  jxl.Sheet;
import  jxl.Workbook;

public class test3 {
	
	public static void main(String[] args) throws IOException{
		String[] str={"A","B","C","D","E","F","G","H","I","J","K","L","M","N","O","P","Q","R","S","T","U","V","W","X","Y","Z","AA","AB","AC","AD","AE","AF","AG","AH","AI","AJ","AK","AL","AM","AN","AO","AP","AQ","AR","AS","AT","AU","AV","AW","AX","AY","AZ","BA","BB","BC","BD","BE"};
        FileInputStream is = new FileInputStream(new File("C://XF_QCQD_ZX_20110607.xls")); 
     
        HSSFWorkbook wb=new HSSFWorkbook(is);
        HSSFSheet childSheet = wb.getSheetAt(0);   
        int rowNum=childSheet.getLastRowNum()+1;
        int size=rowNum-1;
      
        String[][] data=new String[size][60];
        SimpleDateFormat DateFormat=new SimpleDateFormat("yyyy-MM-dd"); 
        for(int j=1;j<rowNum;j++){   
            HSSFRow row = childSheet.getRow(j);    
            int cellNum=row.getLastCellNum(); 
            for(int k=0;k<cellNum;k++)   
            {   
            	HSSFCell cell=row.getCell((short) k);
        		if(cell==null){
        			data[j-1][k]="";
        		}else if(cell.getCellType()==3){
            		data[j-1][k]="";
            	}else if(cell.getCellType()==1){
                		String a=cell.toString();
            			data[j-1][k]=cell.toString();
            	}else if(cell.getCellType()==0){
            		if(HSSFDateUtil.isCellDateFormatted(cell)||k==33||k==21||k==22||k==23)
                    {
                    	data[j-1][k]= DateFormat.format(cell.getDateCellValue());
                    	if(data[j-1][k].length()>19){
                    	  data[j-1][k]= DateFormat.format(cell.getDateCellValue()).substring(0, 19);
                    	}
                    }else{
                    		double a=cell.getNumericCellValue();
                    		data[j-1][k]=a+"";
                    }
            	}else if(HSSFCell.CELL_TYPE_BLANK == cell.getCellType()){
            		data[j-1][k]="";
            	}
//        		  log.info(j-1+"-"+k+":"+data[j-1][k]);
            }     
        }  
        int aaa=0;
        String[] s;  
        s=new String[data.length];	
        for(int i1=0;i1<data.length;i1++){
        	String MANAGECOM=data[i1][2];  //管理机构C
        	String MANAGECOMID=data[i1][3];   //机构代码D
        	String MANAGECOMNAME=data[i1][4];    //机构名称E
        	String SALECHNL=data[i1][5];              //销售渠道F
        	
        	
        	String BQcode=data[i1][6];   //保全项目代码G
        	String BQTYPE=data[i1][7];  // 保全项目H
        	
        	
        	String CONTNO=data[i1][8];   //保单号码I
        	String APPNTNAME=data[i1][9];   // 投保人姓名J
        	String APPNTNO=data[i1][10];    //投保客户号K
        	String APPNTSEX=data[i1][11];        //投保人性别L

        	String APPNTMOBILE=data[i1][12];    //移动电话M
        	String APPNTPHONE=data[i1][13];    //固定电话N
        	String APPNTPHONE1=data[i1][14];    //固定电话O

        	String INUSERDNAME=data[i1][15];    // 被保人姓名P
        	String INSUREDNO=data[i1][16];    //被保客户号Q
        	String INSUREDSET=data[i1][17];   //被保人性别R

          	String INSUERDAGE=data[i1][18];   //被保人年龄S

        	String RELATIONTOAPPNT=data[i1][19];   //与投保人关系T
        	String CONTSTATE=data[i1][20];   //保单状态U
        	

        	String CVALIDATE=data[i1][21];   //生效日期V
        	if(CVALIDATE!=null&&CVALIDATE.length()>3){
        		CVALIDATE="to_date('"+CVALIDATE+"','yyyy-mm-dd')";
        	}else{
        		CVALIDATE="''";
        	}
        	String CUSTOMGETPOLDATE=data[i1][22];   //客户签收日期W
        	if(CUSTOMGETPOLDATE!=null&&CUSTOMGETPOLDATE.length()>3){
        		CUSTOMGETPOLDATE="to_date('"+CUSTOMGETPOLDATE+"','yyyy-mm-dd')";
        	}else{
        		CUSTOMGETPOLDATE="''";
        	}
        	String GETPOLDATE=data[i1][23];   //回单日期X
        	if(GETPOLDATE!=null&&GETPOLDATE.length()>3){
        		GETPOLDATE="to_date('"+GETPOLDATE+"','yyyy-mm-dd')";
        	}else{
        		GETPOLDATE="''";
        	}
        	String RISKCODE=data[i1][24];   //险种编码V
        	String RISKNAME=data[i1][25];   //险种名称Z
        	
        	String CDdate=data[i1][1];  //抽档日期B
        	if(CDdate!=null&&CDdate.length()>3){
        		CDdate="to_date('"+CDdate+"','yyyy-mm-dd')";
        	}else{
        		CDdate="''";
        	}

        	
        	
        	String BFTOTAL=data[i1][26];   //保费合计AA				            	
        	String bakreasom=data[i1][27];   //退保原因AB	
        	String TFmoney=data[i1][28];   //退费金额(元)AC	
        	String TFTYPE=data[i1][29];   //退费方式AD
        	String TFzh=data[i1][30];   //退费账号Ae
        	String SSbankcode=data[i1][31];   //所属银行代码AF
        	String SSbank=data[i1][32];   //所属银行AG
        	String CWQRdate =data[i1][33];   //财务确认日期Ah
        	if(CWQRdate!=null&&CWQRdate .length()>3){
        		CWQRdate ="to_date('"+CWQRdate +"','yyyy-mm-dd')";
        	}else{
        		CWQRdate ="''";
        	}
        	
        	

        	String AGENTZZCODE=data[i1][34];   //业务员部组号AI
        	String AGENTZZB =data[i1][35];   //业务员部组名称AJ
        	String AGENTCODE=data[i1][36];   //业务员代码AK
        	String AGENTNAME=data[i1][37];   //业务员姓名AL
        	String AGENTMOBILE=data[i1][38];   //业务员手机AM
        	
//        	int judge11=0;
//        	String sql22="select count(*) as a from customer where customerno='"+APPNTNO+"'";
//        	log.info("判断customer:"+sql22);
//        	List list1=ftpbean.chaxun(sql22);
//        	if(list1!=null&&list1.size()>0){
//            	Map map=(Map)list1.get(0);
//            	log.info("a:"+(java.math.BigDecimal)map.get("a"));
//            	BigDecimal aa=(java.math.BigDecimal)map.get("a");
//            	judge11=aa.intValue();
//            }
//        	 if(judge11==0){
//        		 String sql222="insert into customer(customer_guid,customername,customerno,TEL_1,TEL_2,mobile) values " +
//        		 		"(sys_guid(),'"+APPNTNAME+"','"+APPNTNO+"','"+APPNTPHONE+"','"+APPNTPHONE1+"','"+APPNTMOBILE+"')";
//		            int aaq=ftpbean.update(sql222);
//		            log.info("是否成功:"+aaq);
//        	 }
        	
        	int judge1=0;
        	String sql11="";
//        	log.info("判断是否要插入新数据:"+" select count(*) as a from XF_VISIT where  CONTNO ='"+CONTNO+"' ");
//        	List list=ftpbean.chaxun(" select count(*) as a from XF_VISIT where  CONTNO ='"+CONTNO+"' ");
//        	if(list!=null&&list.size()>0){
//            	Map map=(Map)list.get(0);
//            	log.info("a:"+(java.math.BigDecimal)map.get("a"));
//            	BigDecimal aa=(java.math.BigDecimal)map.get("a");
//            	judge1=aa.intValue();
//            }
        	
        	sql11="insert into XF_QC(" +
			"MANAGECOM,MANAGECOMID,MANAGECOMNAME,SALECHNL," +
			"CONTNO,CONTSTATE,APPNTNAME,APPNTNO,APPNTSEX," +
			"APPNTMOBILE,APPNTPHONE," +
			"INUSERDNAME,INSUREDNO,INSUREDSET,INSUERDAGE," +
			"RELATIONTOAPPNT,CVALIDATE,CUSTOMGETPOLDATE," +
			"GETPOLDATE,RISKCODE,RISKNAME,AGENTCODE," +
			"AGENTNAME,AGENTMOBILE,BFTOTAL," +
			"AGENTZZCODE,AGENTZZB,CDdate,CWQRDATE,TFMONEY,SSBANK,BAKREASOM,TFZH,APPNTPHONE1,YWTYPE,YWNAME,TFTYPE,SSBANKCODE) Values" +
			"('"+MANAGECOM+"','"+MANAGECOMID+"','"+MANAGECOMNAME+"','"+SALECHNL+"'"+
			",'"+CONTNO+"','"+CONTSTATE+"','"+APPNTNAME+"','"+APPNTNO+"','"+APPNTSEX+"'"+
			",'"+APPNTMOBILE+"','"+APPNTPHONE+"',"+
			"'"+INUSERDNAME+"','"+INSUREDNO+"'," +"'"+INSUREDSET+"','"+INSUERDAGE+
			"','"+RELATIONTOAPPNT+"',"+CVALIDATE+","+CUSTOMGETPOLDATE+
			","+GETPOLDATE+",'"+RISKCODE+"','"+RISKNAME+"','"+AGENTCODE+
			"','"+AGENTNAME+"','"+AGENTMOBILE+"','"+BFTOTAL+
			"',"+
			"'"+AGENTZZCODE+"','"+AGENTZZB+"',"+CDdate+","+CWQRdate+",'"+TFmoney+"','"+SSbank+"','"+bakreasom+"','"+TFzh+"','"+APPNTPHONE1+"','"+BQcode+"','"+BQTYPE+"','"+TFTYPE+"','"+SSbankcode+"')";
        	 
        	System.out.println(sql11);

	}
	}
}
